﻿using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using Infrastructure.Common.Data;
using TallyBook.DesignLib;

namespace TallyBook.DAL.Repository.Imp
{
    public class IncomeRepository : IRepository<Income>
    {
        private readonly IDbHelper _dbHelper = DbFactory.Instance.CreateDbHelper();


        public Income Get(long id)
        {
            var entity = new Income
            {
                Id = id
            };
            string sqlStr = entity.EntityToSqlFromPrimaryKey();
            using (SQLiteDataReader sqlDataReader = _dbHelper.ExecuteReader(_dbHelper.GetConnection(), CommandType.Text,
                sqlStr))
            {
                //判断是否可读，并且在使用数据前一定要运行才能读取
                if (!sqlDataReader.Read())
                    return null;
                entity.SqlToEntity(sqlDataReader);
                return entity;
            }
        }

        public int Insert(Income entity)
        {
            string sqlStr = SqlBuilder.InsertComplex(entity);
            int query = _dbHelper.ExecuteNonQuery(_dbHelper.GetConnection(), CommandType.Text, sqlStr);
            return query;
        }

        public int Update(Income entity)
        {
            string sqlStr = SqlBuilder.UpdateComplex(entity);
            int query = _dbHelper.ExecuteNonQuery(_dbHelper.GetConnection(), CommandType.Text, sqlStr);
            return query;
        }

        public int Delete(Income entity)
        {
            string sqlStr = SqlBuilder.DeleteComplex(entity);
            int query = _dbHelper.ExecuteNonQuery(_dbHelper.GetConnection(), CommandType.Text, sqlStr);
            return query;
        }

        public List<Income> GetAll()
        {
            var entity = new Income();
            string sqlStr = entity.EntityToSqlFromAll();
            using (DataSet dataset = _dbHelper.ExecuteDataset(_dbHelper.GetConnection(), CommandType.Text, sqlStr))
            {
                var list = new List<Income>();

                foreach (DataRow row in dataset.Tables[0].Rows)
                {
                    var newEntity = new Income();
                    newEntity.SqlToEntity(row);
                    list.Add(newEntity);
                }
                return list;
            }
        }

        public List<IncomeType> GetName()
        {
            string sqlStr = SqlBuilder.GetAllComplex<IncomeType>();
            using (DataSet dataset = _dbHelper.ExecuteDataset(_dbHelper.GetConnection(), CommandType.Text, sqlStr))
            {
                var list = new List<IncomeType>();

                foreach (DataRow row in dataset.Tables[0].Rows)
                {
                    var newEntity = new IncomeType
                    {
                        Id = (long) row[0],
                        Name = (string) row[1]
                    };
                    list.Add(newEntity);
                }
                return list;
            }
        }

        public List<Income> GetDim(long incomesource)
        {
            var entity = new Income
            {
                IncomeTypeId = incomesource
            };
            string sqlStr = entity.EntityToSqlFromDim();
            using (DataSet dataset = _dbHelper.ExecuteDataset(_dbHelper.GetConnection(), CommandType.Text, sqlStr))
            {
                var list = new List<Income>();

                foreach (DataRow row in dataset.Tables[0].Rows)
                {
                    var newEntity = new Income();
                    newEntity.SqlToEntity(row);
                    list.Add(newEntity);
                }
                return list;
            }
        }

        public List<Income> GetDim(decimal incomesource)
        {
            var entity = new Income
            {
                Money = incomesource
            };
            string sqlStr = entity.EntityToSqlFromDimDecial();
            using (DataSet dataset = _dbHelper.ExecuteDataset(_dbHelper.GetConnection(), CommandType.Text, sqlStr))
            {
                var list = new List<Income>();

                foreach (DataRow row in dataset.Tables[0].Rows)
                {
                    var newEntity = new Income();
                    newEntity.SqlToEntity(row);
                    list.Add(newEntity);
                }
                return list;
            }
        }
    }
}